﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using System.Windows.Forms;
using dhDataAcess.dataAcess;

namespace baseSet.importExport
{
    public class exportToExcel
    {
        public static MemoryStream RenderToExcel(DataGridView table, sysDataSet.itemListDataTable itemListTable, string title, string moduleName)
        {
            MemoryStream ms = new MemoryStream();
            // itemListBLL itemList = new itemListBLL();
            // basicDataSetxsd.itemListDataTable itemListTable = itemList.getItemListTableByModuleName(moduleName);

            using (IWorkbook workbook = new HSSFWorkbook())
            {
                using (ISheet sheet = workbook.CreateSheet())
                {



                    IRow headerRow = sheet.CreateRow(1);

                    // handling header.
                    int i = 0;
                    foreach (DataGridViewColumn column in table.Columns)
                    {
                        foreach (sysDataSet.itemListRow listRow in itemListTable)
                        {
                            if (listRow.itemName == column.Name && listRow.itemDispNo)
                            {
                                headerRow.CreateCell(i).SetCellValue(listRow.itemDispC);//If Caption not set, returns the ColumnName value
                                i++;
                            }
                        }
                    }
                    // handling value.
                    int rowIndex = 2;

                    foreach (DataGridViewRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        i = 0;
                        foreach (DataGridViewColumn column in table.Columns)
                        {

                            foreach (sysDataSet.itemListRow listRow in itemListTable)
                            {
                                if (listRow.itemName == column.Name && listRow.itemDispNo)
                                {
                                    Type t=null;
                                    if (row.Cells[column.Index].Value==null)
                                    {
                                        row.Cells[column.Index].Value = "";
                                        t = typeof(string);
                                    }
                                    else
                                    {
                                        t = row.Cells[column.Index].Value.GetType();
                                    }
                                    if (t == typeof(int))
                                    {
                                        dataRow.CreateCell(i, CellType.NUMERIC).SetCellValue(Convert.ToInt32(row.Cells[column.Index].Value.ToString()));
                                    }
                                    if(  t==typeof(decimal) || t==typeof(double))
                                    {
                                        dataRow.CreateCell(i, CellType.NUMERIC).SetCellValue(Convert.ToDouble(row.Cells[column.Index].Value.ToString()));

                                    }
                                    if(t==typeof(string ))
                                    {
                                        dataRow.CreateCell(i).SetCellValue(row.Cells[column.Index].Value.ToString());
                                    }

                                    if(t==typeof(DateTime))
                                    {
                                        dataRow.CreateCell(i).SetCellValue(row.Cells[column.Index].Value.ToString());
                                    }
                                    i++;
                                }
                            }
                        }

                        rowIndex++;
                    }
                    IRow titleRow = sheet.CreateRow(0);
                    titleRow.CreateCell(0).SetCellValue(title);
                    sheetAutoFit(table.Columns.Count, sheet);


                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                }
            }

            return ms;
        }

        private static void sheetAutoFit(int maxColumn, ISheet sheet)
        {
            for (int i = 0; i <= maxColumn; i++)
            {
                sheet.AutoSizeColumn(i);
            }


            for (int columnNum = 0; columnNum <= maxColumn; columnNum++)
            {
                int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (sheet.GetRow(rowNum) == null)
                    {
                        currentRow = sheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = sheet.GetRow(rowNum);
                    }

                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                sheet.SetColumnWidth(columnNum, columnWidth * 256);
            }
        }

        public static void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();

                fs.Write(data, 0, data.Length);
                fs.Flush();

                data = null;
            }
        }
    }
}
